package fr.umlv.m2.recipes.ideas.dao;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import fr.umlv.m2.recipes.ideas.bean.RecipeBean;
import fr.umlv.m2.recipes.ideas.dao.interfaces.RecipesConcreteDAOInterface;
import fr.umlv.m2.recipes.ideas.table.enumerations.TableName;

/**
 * DAO uses to handle a Recipe
 * @author Christophe Chillan et Younes Chandarli
 *
 */
public class RecipeDAO implements RecipesConcreteDAOInterface<RecipeBean> {

	private static final String TABLE_RECIPES = TableName.RECIPES.toString();
	private static final String COL_ID = "ID";

	private static final String COL_NAME = "NAME";
	private static final String COL_DESCRIPTION = "DESCRIPTION";
	private static final String COL_VOTES = "VOTES";
	private static final String COL_DIFFICULTY = "DIFFICULTY";
	private static final String COL_PICTURELINK = "PICTURE_LINK";
	private static final String COL_BOOKMARK = "BOOKMARK";

	private static final String COL_FOREIGN_RECIPES_ID = "RECIPES_ID";
	private static final String FOREIGN_KEYWORD_ID = "KEYWORD_ID";
	private static final String KEYWORD_ID = "ID";
	private static final String COL_KEYWORD_NAME = "KEYWORD";

	private static final int NUM_COL_ID = 0;
	private static final int NUM_COL_NAME = 1;
	private static final int NUM_COL_DESCRIPTION = 2;
	private static final int NUM_COL_DIFFICULTY = 3;
	private static final int NUM_COL_VOTES = 4;
	private static final int NUM_COL_PICTURELINK = 5;
	private static final int NUM_COL_BOOKMARK = 6;

	private final StringBuilder CREATE_DB;
	
	/**
	 * Create a Recipe DAO
	 */
	public RecipeDAO() {
		CREATE_DB = new StringBuilder();
		CREATE_DB.append("CREATE TABLE ");
		CREATE_DB.append(TABLE_RECIPES).append(" ");
		CREATE_DB.append("(");
		CREATE_DB.append(COL_ID).append(" INTEGER PRIMARY KEY AUTOINCREMENT, ");
		CREATE_DB.append(COL_NAME).append(" TEXT NOT NULL, ");
		CREATE_DB.append(COL_DESCRIPTION).append(" TEXT, ");
		CREATE_DB.append(COL_DIFFICULTY).append(" INTEGER, ");
		CREATE_DB.append(COL_VOTES).append(" INTEGER, ");
		CREATE_DB.append(COL_PICTURELINK).append(" TEXT, ");
		CREATE_DB.append(COL_BOOKMARK).append(" NUMERIC");
		CREATE_DB.append(");");
	}

	/**
	 * @see SQLiteOpenHelper
	 */
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_DB.toString());
	}

	/**
	 * @see SQLiteOpenHelper
	 */
	public void onUpgrade(SQLiteDatabase db) {
		db.execSQL("DROP TABLE " + TABLE_RECIPES);
		onCreate(db);
	}

	/**
	 * Insert a recipe in the database
	 *  @param db
	 *  @param keyword
	 *  @return long - 1 if the entry has been inserted, 0 otherwise 
	 */
	public long insert(SQLiteDatabase db, RecipeBean recipe) {
		// Création d'un ContentValues (fonctionne comme une HashMap)
		ContentValues values = new ContentValues();
		// Création d'un ContentValues (fonctionne comme une HashMap)
		values.put(COL_NAME, recipe.getName());
		values.put(COL_DESCRIPTION, recipe.getDescription());
		values.put(COL_VOTES, recipe.getVotes());
		values.put(COL_DIFFICULTY, recipe.getDifficulty());
		values.put(COL_PICTURELINK, recipe.getPictureLink());
		values.put(COL_BOOKMARK, recipe.getBookmark());
		return db.insert(TABLE_RECIPES, null, values);
	}

	/**
	 * update a recipe from the recipe id in parameters
	 * @param db
	 * @param recipe
	 * @param id
	 * @return integer - the number of entries uploaded
	 */
	public int update(SQLiteDatabase db, RecipeBean recipe, int id) {
		// La mise à jour d'une recette la BDD fonctionne plus ou moins comme
		// une insertion
		// il faut simple préciser quelle recette on doit mettre à jour grâce à
		// l'ID

		ContentValues values = new ContentValues();
		values.put(COL_NAME, recipe.getName());
		values.put(COL_DESCRIPTION, recipe.getDescription());
		values.put(COL_VOTES, recipe.getVotes());
		values.put(COL_DIFFICULTY, recipe.getDifficulty());
		values.put(COL_PICTURELINK, recipe.getPictureLink());
		values.put(COL_BOOKMARK, recipe.getBookmark());
		return db.update(TABLE_RECIPES, values, COL_ID + " = " + id, null);
	}

	/**
	 * remove a recipe from the id
	 * @return integer - the number of entries removed
	 */
	public int removeWithId(SQLiteDatabase db, int id) {
		return db.delete(TABLE_RECIPES, COL_ID + " = " + id, null);
	}

	/**
	 * Get the recipe from its name
	 * @param db
	 * @param name
	 * @return the recipe in the database
	 */
	public RecipeBean getRecipeWithName(SQLiteDatabase db, String name) {
		Cursor c = db.query(TABLE_RECIPES, new String[] { COL_ID, COL_NAME,
				COL_DESCRIPTION, COL_DIFFICULTY, COL_VOTES, COL_PICTURELINK,
				COL_BOOKMARK }, COL_NAME + " LIKE \"" + name + "\"", null,
				null, null, null);
		return cursorToRecipes(c);
	}
	
	/**
	 * Get the recipe from its id in the database
	 * @param db
	 * @param id
	 * @return the recipe in the database
	 */
	public RecipeBean getRecipeWithId(SQLiteDatabase db, int id) {
		Cursor c = db.query(TABLE_RECIPES, new String[] { COL_ID, COL_NAME,
				COL_DESCRIPTION, COL_DIFFICULTY, COL_VOTES, COL_PICTURELINK,
				COL_BOOKMARK }, COL_NAME + " = " + id, null,
				null, null, null);
		return cursorToRecipes(c);
	}
	
	/**
	 * Get the favorites recipes
	 * @param db
	 * @return A {@link List} of the favorites recipes
	 */
	public List<RecipeBean> getFavoritesRecipes(SQLiteDatabase db){
		Cursor c = db.query(TABLE_RECIPES, new String[] { COL_ID, COL_NAME,
				COL_DESCRIPTION, COL_DIFFICULTY, COL_VOTES, COL_PICTURELINK,
				COL_BOOKMARK }, COL_BOOKMARK + " = 1", null,
				null, null, null);
		return cursorToAllRecipes(c);
	}
	
	/**
	 * Get the favorites recipes ordered by name 
	 * @param db
	 * @return A {@link List} of the favorites recipes
	 */
	public List<RecipeBean> getFavoritesRecipesOrderedByName(SQLiteDatabase db){
		Cursor c = db.query(TABLE_RECIPES, new String[] { COL_ID, COL_NAME,
				COL_DESCRIPTION, COL_DIFFICULTY, COL_VOTES, COL_PICTURELINK,
				COL_BOOKMARK }, COL_BOOKMARK + " = 1", null,
				null, null, COL_NAME + " ASC");
		return cursorToAllRecipes(c);
	}
	
	/**
	 * Get the favorites recipes ordered by difficulty 
	 * @param db
	 * @return A {@link List} of the favorites recipes
	 */
	public List<RecipeBean> getFavoritesRecipesOrderedByDifficulty(SQLiteDatabase db){
		Cursor c = db.query(TABLE_RECIPES, new String[] { COL_ID, COL_NAME,
				COL_DESCRIPTION, COL_DIFFICULTY, COL_VOTES, COL_PICTURELINK,
				COL_BOOKMARK }, COL_BOOKMARK + " = 1", null,
				null, null, COL_DIFFICULTY + " ASC," + COL_NAME + " ASC");
		return cursorToAllRecipes(c);
	}
	
	/**
	 * Get the favorites recipes ordered by vote 
	 * @param db
	 * @return A {@link List} of the favorites recipes
	 */
	public List<RecipeBean> getFavoritesRecipesOrderedByVote(SQLiteDatabase db){
		Cursor c = db.query(TABLE_RECIPES, new String[] { COL_ID, COL_NAME,
				COL_DESCRIPTION, COL_DIFFICULTY, COL_VOTES, COL_PICTURELINK,
				COL_BOOKMARK }, COL_BOOKMARK + " = 1", null,
				null, null, COL_VOTES + " ASC," + COL_NAME + " ASC");
		return cursorToAllRecipes(c);
	}
	
	/**
	 * Get all recipes from the database
	 * @param db
	 * @return every recipe of the database
	 */
	public List<RecipeBean> getAllRecipes(SQLiteDatabase db) {
		Cursor c = db.query(TABLE_RECIPES, new String[] { COL_ID, COL_NAME,
				COL_DESCRIPTION, COL_DIFFICULTY, COL_VOTES, COL_PICTURELINK,
				COL_BOOKMARK }, null, null,
				null, null, null);
		return cursorToAllRecipes(c);
	}
	
	/**
	 * Get all recipes from the database ordered by name
	 * @param db
	 * @return every recipe of the database
	 */
	public List<RecipeBean> getAllRecipesOrderedByName(SQLiteDatabase db) {
		Cursor c = db.query(TABLE_RECIPES, new String[] { COL_ID, COL_NAME,
				COL_DESCRIPTION, COL_DIFFICULTY, COL_VOTES, COL_PICTURELINK,
				COL_BOOKMARK }, null, null,
				null, null, COL_NAME + " ASC");
		return cursorToAllRecipes(c);
	}
	
	/**
	 * Get all recipes from the database ordered by difficulty
	 * @param db
	 * @return every recipe of the database
	 */
	public List<RecipeBean> getAllRecipesOrderedByDifficulty(SQLiteDatabase db) {
		Cursor c = db.query(TABLE_RECIPES, new String[] { COL_ID, COL_NAME,
				COL_DESCRIPTION, COL_DIFFICULTY, COL_VOTES, COL_PICTURELINK,
				COL_BOOKMARK }, null, null,
				null, null, COL_DIFFICULTY + " ASC," + COL_NAME + " ASC" );
		return cursorToAllRecipes(c);
	}
	
	/**
	 * Get all recipes from the database ordered by votes
	 * @param db
	 * @return every recipe of the database
	 */
	public List<RecipeBean> getAllRecipesOrderedByVote(SQLiteDatabase db) {
		Cursor c = db.query(TABLE_RECIPES, new String[] { COL_ID, COL_NAME,
				COL_DESCRIPTION, COL_DIFFICULTY, COL_VOTES, COL_PICTURELINK,
				COL_BOOKMARK }, null, null,
				null, null, COL_VOTES + " ASC," + COL_NAME + " ASC" );
		return cursorToAllRecipes(c);
	}
	
	

	/**
	 * Get a recipe from a keyword
	 * @param db
	 * @param keyword
	 * @return All the recipe from the keyword
	 */
	public List<RecipeBean> getRecipeByKeyword(SQLiteDatabase db, String keyword) {
		StringBuilder query = new StringBuilder();
		query.append("SELECT DISTINCT");
		query.append(COL_ID).append(",");
		query.append(COL_NAME).append(",");
		query.append(COL_DESCRIPTION).append(",");
		query.append(COL_DIFFICULTY).append(",");
		query.append(COL_VOTES).append(",");
		query.append(COL_PICTURELINK).append(",");
		query.append(COL_BOOKMARK).append(" ");
		query.append("FROM ").append(TABLE_RECIPES).append(" ")
				.append(TableName.KEYWORD.toString()).append(" ")
				.append(TableName.RECIPESTOKEYWORD.toString()).append(" ");
		query.append("WHERE ");
		query.append(TABLE_RECIPES).append(".").append(COL_ID);
		query.append("=").append(TableName.RECIPESTOKEYWORD.toString())
				.append(".").append(COL_FOREIGN_RECIPES_ID);
		query.append("AND ").append(TableName.KEYWORD.toString()).append(".")
				.append(KEYWORD_ID);
		query.append("=").append(TableName.RECIPESTOKEYWORD.toString())
				.append(".").append(FOREIGN_KEYWORD_ID);
		query.append("AND ").append(TableName.KEYWORD.toString()).append(".")
				.append(COL_KEYWORD_NAME);
		query.append("=").append(keyword);
		Cursor c = db.rawQuery(query.toString(), null);
		return cursorToAllRecipes(c);
	}
	
	/**
	 * Get the recipe from the search engine expression
	 * @param db
	 * @param name
	 * @return every recipe related to the expression
	 */
	public List<RecipeBean> getRecipeBySearchEngine(SQLiteDatabase db, String name, String[] keywords, List<String> ingredients){
		List<RecipeBean> recipes = new ArrayList<RecipeBean>();
		recipes.addAll(searchRecipeByName(db, name));
		recipes.addAll(searchByKeyword(db, keywords));
		recipes.addAll(searchByIngredient(db, ingredients));
		return recipes;
	}
	
	private List<RecipeBean> searchRecipeByName(SQLiteDatabase db, String name){
		if(name.trim().length()==0 ){
			List<RecipeBean> recipes = new ArrayList<RecipeBean>(0);
			return recipes;
		}
		StringBuilder query = new StringBuilder();		
		query.append("SELECT DISTINCT ");
		query.append("r.").append(COL_ID).append(",");
		query.append("r.").append(COL_NAME).append(",");
		query.append("r.").append(COL_DESCRIPTION).append(",");
		query.append(COL_DIFFICULTY).append(",");
		query.append(COL_VOTES).append(",");
		query.append(COL_PICTURELINK).append(",");
		query.append(COL_BOOKMARK).append(" ");
		query.append("FROM ");
		query.append("RECIPES as r ");
		query.append("WHERE ");
		query.append("lower(r.name) LIKE lower('%").append(name).append("%')");
		Cursor c = db.rawQuery(query.toString(), null);
		return cursorToAllRecipes(c);
	}
	
	private List<RecipeBean> searchByIngredient(SQLiteDatabase db, List<String> ingredients){
		if(ingredients.size()==0){
			List<RecipeBean> recipes = new ArrayList<RecipeBean>(0);
			return recipes;
		}
		StringBuilder query = new StringBuilder();
		int i;
		query.append("SELECT DISTINCT ");
		query.append("r.").append(COL_ID).append(",");
		query.append("r.").append(COL_NAME).append(",");
		query.append("r.").append(COL_DESCRIPTION).append(",");
		query.append(COL_DIFFICULTY).append(",");
		query.append(COL_VOTES).append(",");
		query.append(COL_PICTURELINK).append(",");
		query.append(COL_BOOKMARK).append(" ");
		query.append("FROM ");
		query.append("RECIPES as r ");
		query.append("JOIN  RECIPESTOINGREDIENT as ri ON r.id = ri.recipes_id ");
		query.append("JOIN  INGREDIENT as i ON i.id = ri.ingredient_id ");
		query.append("WHERE ");
		for(i=0;i<ingredients.size();i++){
			if(i>0 && i<ingredients.size()-1){
				query.append("OR ");
			}
			query.append("lower(i.name) LIKE lower('%").append(ingredients.get(i)).append("%') ");
		}
		Cursor c = db.rawQuery(query.toString(), null);
		return cursorToAllRecipes(c);
	}
	
	private List<RecipeBean> searchByKeyword(SQLiteDatabase db, String[] keywords){
		if(keywords.length==0){
			List<RecipeBean> recipes = new ArrayList<RecipeBean>(0);
			return recipes;
		}
		
		int i=0;
		StringBuilder query = new StringBuilder();
		query.append("SELECT DISTINCT ");
		query.append("r.").append(COL_ID).append(",");
		query.append("r.").append(COL_NAME).append(",");
		query.append("r.").append(COL_DESCRIPTION).append(",");
		query.append(COL_DIFFICULTY).append(",");
		query.append(COL_VOTES).append(",");
		query.append(COL_PICTURELINK).append(",");
		query.append(COL_BOOKMARK).append(" ");
		query.append("FROM ");
		query.append("RECIPES as r ");
		query.append("JOIN RECIPESTOKEYWORD as rk ON r.id = rk.recipes_id ");
		query.append("JOIN KEYWORD as k ON k.id = rk.keyword_id ");
		query.append("WHERE ");
		for(i=0;i<keywords.length;i++){
			if(i>0 && i<keywords.length-1){
				query.append("OR ");
			}
			query.append("lower(k.keyword) LIKE lower('%").append(keywords[i]).append("%') ");
		}
		Cursor c = db.rawQuery(query.toString(), null);
		return cursorToAllRecipes(c);
		
	}
	/**
	 * Get the recipe by price order
	 * @param db
	 * @return get all the recipes ordered by price and by name 
	 */
	public List<RecipeBean> getAllRecipesOrderedByPrice(SQLiteDatabase db){
		StringBuilder query = new StringBuilder();
		query.append("SELECT DISTINCT ");
		query.append("r.").append(COL_ID).append(",");
		query.append("r.").append(COL_NAME).append(",");
		query.append("r.").append(COL_DESCRIPTION).append(",");
		query.append(COL_DIFFICULTY).append(",");
		query.append(COL_VOTES).append(",");
		query.append(COL_PICTURELINK).append(",");
		query.append(COL_BOOKMARK).append(",");
		query.append("SUM(i.price) as price").append(" ");
		query.append("FROM ");
		query.append("RECIPES as r ");
		query.append("LEFT JOIN  RECIPESTOINGREDIENT as ri ON r.id = ri.recipes_id ");
		query.append("LEFT JOIN  INGREDIENT as i ON i.id = ri.ingredient_id ");
		query.append("GROUP BY r.").append(COL_ID).append(",");
		query.append("r.").append(COL_NAME).append(",");
		query.append("r.").append(COL_DESCRIPTION).append(",");
		query.append(COL_DIFFICULTY).append(",");
		query.append(COL_VOTES).append(",");
		query.append(COL_PICTURELINK).append(",");
		query.append(COL_BOOKMARK).append(" ");
		query.append("ORDER BY price, ").append("r.").append(COL_NAME).append(" ASC");
		Cursor c = db.rawQuery(query.toString(), null);
		return cursorToAllRecipes(c);
		
	}
	
	/**
	 * Get the favorite recipes by price order
	 * @param db
	 * @return get all the recipes ordered by price and by name 
	 */
	public List<RecipeBean> getFavoriteRecipesOrderedByPrice(SQLiteDatabase db){
		StringBuilder query = new StringBuilder();
		query.append("SELECT DISTINCT ");
		query.append("r.").append(COL_ID).append(",");
		query.append("r.").append(COL_NAME).append(",");
		query.append("r.").append(COL_DESCRIPTION).append(",");
		query.append(COL_DIFFICULTY).append(",");
		query.append(COL_VOTES).append(",");
		query.append(COL_PICTURELINK).append(",");
		query.append(COL_BOOKMARK).append(",");
		query.append("SUM(i.price) as price").append(" ");
		query.append("FROM ");
		query.append("RECIPES as r ");
		query.append("LEFT JOIN  RECIPESTOINGREDIENT as ri ON r.id = ri.recipes_id ");
		query.append("LEFT JOIN  INGREDIENT as i ON i.id = ri.ingredient_id ");
		query.append("WHERE ").append(COL_BOOKMARK).append("=1 ");
		query.append("GROUP BY r.").append(COL_ID).append(",");
		query.append("r.").append(COL_NAME).append(",");
		query.append("r.").append(COL_DESCRIPTION).append(",");
		query.append(COL_DIFFICULTY).append(",");
		query.append(COL_VOTES).append(",");
		query.append(COL_PICTURELINK).append(",");
		query.append(COL_BOOKMARK).append(" ");
		query.append("ORDER BY price, ").append("r.").append(COL_NAME).append(" ASC");
		Cursor c = db.rawQuery(query.toString(), null);
		return cursorToAllRecipes(c);
		
	}


	/**
	 * Get the last id of the recipes
	 * @param db
	 * @return integer - the last id inserted in the database
	 */
	public int getLastInsertIdOfRecipe(SQLiteDatabase db) {
		StringBuilder query = new StringBuilder();
		query.append("SELECT max(").append(COL_ID).append(") ");
		query.append("from ").append(TABLE_RECIPES);
		Cursor c = db.rawQuery(query.toString(), null);
		return cursorToMaxId(c);
	}

	/**
	 * Convert a cursor in a Recipe
	 * 
	 * @param c
	 * @return
	 */
	private RecipeBean cursorToRecipes(Cursor c) {
		if (c.getCount() == 0) {
			return null;
		}
		c.moveToFirst();

		RecipeBean recipe = new RecipeBean();

		recipe.setId(c.getInt(NUM_COL_ID));
		recipe.setName(c.getString(NUM_COL_NAME));
		recipe.setDescription(c.getString(NUM_COL_DESCRIPTION));
		recipe.setDifficulty(c.getInt(NUM_COL_DIFFICULTY));
		recipe.setVotes(c.getInt(NUM_COL_VOTES));
		recipe.setPictureLink(c.getString(NUM_COL_PICTURELINK));
		recipe.setBookmark(c.getShort(NUM_COL_BOOKMARK));
		return recipe;

	}
	
	
	private List<RecipeBean> cursorToAllRecipes(Cursor c) {
		List<RecipeBean> recipes = new ArrayList<RecipeBean>();
		if(!(c.getCount()==0)){
		c.moveToFirst();
		
			do{
				RecipeBean recipe = new RecipeBean();
				recipe.setId(c.getInt(NUM_COL_ID));
				recipe.setName(c.getString(NUM_COL_NAME));
				recipe.setDescription(c.getString(NUM_COL_DESCRIPTION));
				recipe.setDifficulty(c.getInt(NUM_COL_DIFFICULTY));
				recipe.setVotes(c.getInt(NUM_COL_VOTES));
				recipe.setPictureLink(c.getString(NUM_COL_PICTURELINK));
				recipe.setBookmark(c.getShort(NUM_COL_BOOKMARK));
				recipes.add(recipe);
				c.moveToNext();
			}while(!c.isAfterLast());
		}

		
		return Collections.unmodifiableList(recipes);

	}
	
	private int cursorToMaxId(Cursor c){
		if(c.getCount()==0){
			return 0;
		}
		c.moveToFirst();
		return c.getInt(NUM_COL_ID);
	}

	

	


}
